﻿--客户统计
CREATE proc [dbo].[proc_Customer_TongJi]
(
	@startIndex int,
	@endIndex int,
	@docount bit,
	@departmentId int,
	@ename nvarchar(50),
	@CustomerName nvarchar(50),
	@publicityDemandId nvarchar(4),
	@publicityToolId nvarchar(4),
	@networkConsciousnessId nvarchar(4),
	@IntentionsId nvarchar(4),
	@username nvarchar(10)
)
as

--公司Id
declare @companyid varchar (4)
set @companyid = (select companyid from aspnet_Users where username=@username)


declare @tempTable table(id int identity(1,1),customerid nvarchar(50),subjionid1 int,subjionid2 int,subjionid3 int,subjionid4 int)
insert into @tempTable(customerid,subjionid1,subjionid2,subjionid3,subjionid4)
select 
	 customerid,
	(select count(id) from Customer_Subjoin_List cs  where customer.customerid = cs.customerid and subjoinid=1) as subjoinid1,
	 (select count(id) from Customer_Subjoin_List cs  where customer.customerid = cs.customerid and subjoinid=2) as subjoinid2,
	(select count(id) from Customer_Subjoin_List cs  where customer.customerid = cs.customerid and subjoinid=3) as subjoinid3,
	(select count(id) from Customer_Subjoin_List cs  where customer.customerid = cs.customerid and subjoinid=4) as subjoinid4
from 
	customer


set nocount on
	if(@docount=1)
		begin
		select  id,CustomerId,CustomerName,'' as DepartmentName,'' as ename,'AA' as IntentionsName,'' as Grade,PublicityDemandId,NetworkConsciousnessId,'' as GenJinTime,'' ToolCommentTime from customer
		where
			((select Parentid from department where id=(select departmentid from employee e where e.username=protectname)) = @departmentId
	    		or(select Id from department where id=(select departmentid from employee e where e.username=protectname)) = @departmentId)
			and (select ename from employee e where e.username=protectname) like '%'+@ename+'%'
			and CustomerName like '%'+@CustomerName+'%'
			and publicityDemandId like @publicityDemandId
			and (case when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid2 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid3 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid4 from @tempTable t where t.customerid = customer.customerid)=0) then 1
				  when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid2 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid3 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid4 from @tempTable t where t.customerid = customer.customerid)>0) then 3
				  when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid2 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid3 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid4 from @tempTable t where t.customerid = customer.customerid)>0) then 2 end) like @publicityToolId
				 
			and networkConsciousnessId like @networkConsciousnessId
			and IntentionsId like @IntentionsId
			--and  (Convert(varchar(10),(select top 1 uptime from Customer_Track ct where ct.Customerid = customer.CustomerId order by ct.uptime desc),120)>=@genJinTimeFrom and Convert(varchar(10),(select top 1 uptime from Customer_Track ct where ct.Customerid = customer.CustomerId order by ct.uptime desc),120)<=@genJinTimeTo)
			     
			and  CompanyId = @companyid
		end
	else
	begin
	declare @indextable table(id int identity(1,1),nid int)
	set rowcount @endIndex
	insert into @indextable(nid) select id from customer 
	where
			((select Parentid from department where id=(select departmentid from employee e where e.username=protectname)) = @departmentId
	    		or(select Id from department where id=(select departmentid from employee e where e.username=protectname)) = @departmentId)
			and (select ename from employee e where e.username=protectname) like '%'+@ename+'%'
			and CustomerName like '%'+@CustomerName+'%'
			and publicityDemandId like @publicityDemandId
			and (case when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid2 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid3 from @tempTable t where t.customerid = customer.customerid)=0 and (select subjionid4 from @tempTable t where t.customerid = customer.customerid)=0) then 1
				  when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid2 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid3 from @tempTable t where t.customerid = customer.customerid)>0 and (select subjionid4 from @tempTable t where t.customerid = customer.customerid)>0) then 3
				  when ((select subjionid1 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid2 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid3 from @tempTable t where t.customerid = customer.customerid)>0 or (select subjionid4 from @tempTable t where t.customerid = customer.customerid)>0) then 2 end) like @publicityToolId
				 
			and networkConsciousnessId like @networkConsciousnessId
			and IntentionsId like @IntentionsId
			--and  (Convert(varchar(10),(select top 1 uptime from Customer_Track ct where ct.Customerid = customer.CustomerId order by ct.uptime desc),120)>=@genJinTimeFrom and Convert(varchar(10),(select top 1 uptime from Customer_Track ct where ct.Customerid = customer.CustomerId order by ct.uptime desc),120)<=@genJinTimeTo)
			     
			and  CompanyId = @companyid 
select 
	customer.id,
	customer.CustomerId,
	customer.CustomerName,
	(select dname from department where id=(select departmentid from employee e where e.username=protectname)) as DepartmentName,
	(select ename from employee e where e.username=protectname) as ename,
	customer.PublicityDemandId,
	customer.NetworkConsciousnessId,
	customer.IntentionsId,
	(case when customer.IntentionsId <> 0 then (select ci.title from Customer_Intentions ci where customer.IntentionsId=ci.id) else ' ' end)as IntentionsName,
	(select top 1  ct.uptime from Customer_Track ct where ct.Customerid = customer.CustomerId order by ct.uptime desc) as GenJinTime,
	(select top 1  cs.uptime from Customer_Subjoin_List cs where cs.Customerid = customer.CustomerId order by cs.uptime desc) as ToolCommentTime

from customer
	inner join @indextable t on 
	customer.id=t.nid
	where 
		t.id between @startIndex and @endIndex 
	order by t.id
	end
set nocount off
RETURN
